﻿using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.SqlEnum;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace AppManager
{
    public partial class FrmNewTable : Form
    {
        List<MetaTableField> tableFields = null;
        public FrmNewTable()
        {
            InitializeComponent();
            tableFields = new List<MetaTableField>();
        }

        private FieldDataType GetFieldDataType(string dataType)
        {
            FieldDataType result = (FieldDataType)Enum.Parse(typeof(FieldDataType), dataType);

            return result;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                MetaTable metaTable = new MetaTable()
                {
                    Name = txtName.Text
                };

                metaTable.Fields = new List<MetaTableField>();

                Server srv = new Server("(local)");
                Database db = srv.Databases["FlexiCRM"];
                
                Table tb = new Table(db, txtName.Text);
                Column identityCol = new Column(tb, "AutoID", DataType.Int, false);
                identityCol.Identity = true;
                identityCol.IdentityIncrement = 1000;
                identityCol.IdentitySeed = 1;

                tb.Columns.Add(identityCol);
                string columnName = "";
                ushort length = 0;
                string dataType = "";
                bool notnull = false;
                string options = "";
                string label = "";
                foreach (DataGridViewRow gridRow in GridFields.Rows)
                {
                    columnName = "";
                    length = 0;
                    dataType = "";
                    notnull = false;
                    options = "";
                    label = "";
                    if (gridRow.Cells[0].Value != null)
                    {
                        columnName = gridRow.Cells[0].Value.ToString();
                        label = gridRow.Cells[1].Value.ToString();
                        if (gridRow.Cells[3].Value != null)
                        {
                            length = Convert.ToUInt16(gridRow.Cells[3].Value);
                        }
                        else
                        {
                            length = 0;
                        }

                        dataType = gridRow.Cells[2].Value.ToString();
                        notnull = Convert.ToBoolean(gridRow.Cells[5].Value);

                        Column col = new Column(tb, columnName, GetSqlDataType(dataType, length), false);
                        col.Nullable = !notnull;
                        tb.Columns.Add(col);

                        MetaTableField field = new MetaTableField()
                        {
                            DataType = GetFieldDataType(dataType),
                            FieldLength = length,
                            Name = columnName,
                            NotNull = !notnull,
                            Options = options
                        };
                        field.Labels = new List<MetaLanguage>();
                        field.Labels.Add(new MetaLanguage() { LabelText = label, LangID = "en-US" });
                        metaTable.Fields.Add(field);
                    }
                }

                Index primaryKeyIndex = new Index(tb, string.Format("PK_{0}_No", txtName.Text));
                primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
                primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, "No"));
                tb.Indexes.Add(primaryKeyIndex);

                Column ownerCol = new Column(tb, "Owner", DataType.VarChar(30), false);
                ownerCol.Nullable = true;
                tb.Columns.Add(ownerCol);
                Column createdDateCol = new Column(tb, "CreatedDate", DataType.DateTime, false);
                createdDateCol.Nullable = false;
                tb.Columns.Add(createdDateCol);
                Column createdByCol = new Column(tb, "CreatedBy", DataType.VarChar(30), false);
                createdByCol.Nullable = false;
                tb.Columns.Add(createdByCol);
                Column modifiedDateCol = new Column(tb, "ModifiedDate", DataType.DateTime, false);
                modifiedDateCol.Nullable = true;
                tb.Columns.Add(modifiedDateCol);
                Column modifiedByCol = new Column(tb, "ModifiedBy", DataType.VarChar(30), false);
                modifiedByCol.Nullable = true;
                tb.Columns.Add(modifiedByCol);

                tb.Create();

                string tableXml = Utility.SerializeObject(metaTable);
                AppObject createAppObject = new AppObject()
                {
                    CreatedDate = DateTime.Now,
                    Metadata = tableXml,
                    ModuleID = Convert.ToInt32(txtModuleID.Text),
                    Name = txtName.Text.ToUpper().Trim(),
                    ObjectType = "TABLE",
                };

                using (CRMDataContext dataContext = new CRMDataContext())
                {
                    dataContext.AppObjects.InsertOnSubmit(createAppObject);
                    dataContext.SubmitChanges();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private DataType GetSqlDataType(string dataType, int length)
        {
            DataType result = new DataType();
            switch (dataType)
            {
                case "Key":
                    result = DataType.VarChar(30);
                    break;
                case "Text":
                    result = DataType.NVarChar(length);
                    break;
                case "MultilineText":
                    result = DataType.NVarChar(length);
                    break;
                case "MaxText":
                    result = DataType.NText;
                    break;
                case "Email":
                    result = DataType.NVarChar(100);
                    break;
                case "URL":
                    result = DataType.NVarChar(100);
                    break;
                case "Number":
                    result = DataType.Int;
                    break;
                case "SmallNumber":
                    result = DataType.SmallInt;
                    break;
                case "Decimal":
                    result = DataType.Decimal(18, 2);
                    break;
                case "Bool":
                    result = DataType.Bit;
                    break;
                case "Date":
                    result = DataType.DateTime;
                    break;
                case "Select":
                    result = DataType.NVarChar(30);
                    break;
                case "Table":
                    result = DataType.NVarChar(30);
                    break;
                default:
                    break;
            }

            return result;
        }
    }
}
